from tools.db_utils import db_operation, execute_single_value_query, insert_single_row_and_get_id  # 修改导入
from tools.get_week_day_start_end_from_str import get_week_day_start_end_from_str
from tools.sort_excel_columns import sort_excel_columns
from tools.map_list_of_dictionary import map_list_of_dictionary
from tools.split_column import split_column

SQL_插入考勤表 = "INSERT INTO attendance (clazz_id,course_schedule_id) VALUES (%(clazz_id)s,%(course_schedule_id)s)"

SQL_插入考勤明细 = "INSERT INTO attendance_detail (parent_id,student_id,status) VALUES (%(parent_id)s,%(student_id)s,%(status)s)"

SQL_查询课堂安排ID= "select id from course_schedule where week=%(week)s and day=%(day)s and start=%(start)s and course_name=%(course_name)s"

SQL_根据学号查ID="select id from student where number=%(student_number)s"




def insertAttendances(attendance_data,clazz_name):

    clazz_id = execute_single_value_query(f"select id from clazz where name= '{clazz_name}'")

    first_row=attendance_data.pop(0);
    sorted_columns = sort_excel_columns(first_row.keys())

    first_row["course_name"] = "[C07165]数据库分析与设计"
    first_row['clazz_id'] = clazz_id
    for i in range(4, len(sorted_columns), 2):
        column = sorted_columns[i]
        week_day_start=get_week_day_start_end_from_str(first_row[column])
        first_row.update(week_day_start)
        course_schedule_id = execute_single_value_query(SQL_查询课堂安排ID, **first_row)
        first_row['course_schedule_id'] = course_schedule_id
        attendance_id=insert_single_row_and_get_id(SQL_插入考勤表, **first_row)
        for row in attendance_data:
            row['student_number']=row['B']
            row['course_schedule_id'] = course_schedule_id
            row.update(week_day_start)
            row['student_id'] = execute_single_value_query(SQL_根据学号查ID, **row)
            row['status']=row[sorted_columns[i+1]]
            row['parent_id']=attendance_id
            insert_single_row_and_get_id(SQL_插入考勤明细, **row)



    return f'SELECT COUNT(*) FROM attendance where clazz_id={clazz_id}'
